"""
Case Type   : fullsql
Case Name   : track_stmt_stat_level为OFF,L2信息记录
Create At   : 2022/02/08
Owner       : zhao-pengcheng-g
Description :
    1.检查参数，修改配置:enable_copy_server_files=on
    2.检查参数，修改配置:instr_unique_sql_count=100000
    3.创建用户，创建表
    4.导入数据
    5.设置track_stmt_stat_level的值为OFF,L2
    设置log_min_duration_statement=0,使用普通用户执行query
    6.查询statement_history系统表信息
    7.查询full sql函数
    8.查询slow sql函数
    9.查询statement_history minus all dbe_perf.statement_history的数据
    10.环境清理
Expect      :
    1.修改参数成功
    2.修改参数成功
    3.创建成功
    4.导入成功
    5..设置成功,执行query成功，4条数据
    6.有记录，记录日志级别正确
    7.没有记录
    8.没有记录
    9.没有记录
    10.环境清理成功
History     :
"""

import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Case0033 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.rootNode = Node('PrimaryRoot')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_name_1 = 't_customer_fullsql_0033_1'
        self.table_name_2 = 't_orders_fullsql_0033_2'
        self.table_name_3 = 't_lineitem_fullsql_0033_3'
        self.user_name = f'u_fullsql_0033'
        self.table_path = os.path.join(r'/home',
                                       self.userNode.ssh_user, 'copy_tables')

        text = '---prestep1:修改机器umask---'
        self.logger.info(text)
        umask_cmd = f"umask 0022;"
        self.rootNode.sh(umask_cmd).result()
        text = '---step1:检查参数，修改配置:enable_copy_server_files=on,' \
               'expect:成功---'
        self.logger.info(text)
        self.config_copy = 'enable_copy_server_files'
        self.show_copy_cmd = f'show {self.config_copy};'
        check_copy = self.sh_primy.execut_db_sql(self.show_copy_cmd)
        self.logger.info(check_copy)
        self.copy_default = check_copy.splitlines()[-2].strip()
        if 'on' != self.copy_default:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_copy}=on')
            self.assertTrue(result, '参数修改失败')
        chk_copy_msg = self.sh_primy.execut_db_sql(self.show_copy_cmd)
        self.logger.info(chk_copy_msg)
        self.common.equal_sql_mdg(chk_copy_msg, self.config_copy, 'on',
                                  '(1 row)', flag='1')

        text = '---step2:检查参数，修改配置:instr_unique_sql_count=100000,' \
               'expect:成功---'
        self.logger.info(text)
        self.config_instr = 'instr_unique_sql_count'
        self.show_instr_cmd = f'show {self.config_instr};'
        check_instr = self.sh_primy.execut_db_sql(self.show_instr_cmd)
        self.logger.info(check_instr)
        self.instr_default = check_instr.splitlines()[-2].strip()
        if '100000' != self.instr_default:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_instr}=100000')
            self.assertTrue(result, '参数修改失败')
        chk_instr_msg = self.sh_primy.execut_db_sql(self.show_instr_cmd)
        self.logger.info(chk_instr_msg)
        self.common.equal_sql_mdg(chk_instr_msg, self.config_instr, '100000',
                                  '(1 row)', flag='1')

        text = '---step3:清理历史记录,创建用户，创建表,expect;成功---'
        self.logger.info(text)
        sql_cmd = f'''select reset_unique_sql('global', 'all', 1);
                    drop user if exists {self.user_name} cascade;
                    create user {self.user_name} sysadmin \
                    password '{macro.COMMON_PASSWD}'; '''
        self.logger.info(sql_cmd)
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn('reset_unique_sql' and '(1 row)',
                      sql_msg, '执行失败:' + text)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG and
                      self.constant.CREATE_ROLE_SUCCESS_MSG,
                      sql_msg, '执行失败:' + text)

        text = '---step3.1:切换用户,创建customer表,expect:创建成功 ---'
        self.logger.info(text)
        create_customer = f'''set role {self.user_name} \
                password '{macro.COMMON_PASSWD}';
                drop table if exists {self.table_name_1};
                create table {self.table_name_1}
                ( c_custkey     bigint not null, 
                c_name        varchar(25) not null, 
                c_address     varchar(40) not null, 
                c_nationkey   int not null, 
                c_phone       char(15) not null, 
                c_acctbal     decimal(15,2)   not null, 
                c_mktsegment  char(10) not null, 
                c_comment     varchar(117) not null
                )
                partition by range(c_nationkey)
                (
                partition c_nationkey_1 values less than(1),
                partition c_nationkey_2 values less than(2),
                partition c_nationkey_3 values less than(3),
                partition c_nationkey_4 values less than(4),
                partition c_nationkey_5 values less than(5),
                partition c_nationkey_6 values less than(6),
                partition c_nationkey_7 values less than(7),
                partition c_nationkey_8 values less than(8),
                partition c_nationkey_9 values less than(9),
                partition c_nationkey_10 values less than(10),
                partition c_nationkey_11 values less than(11),
                partition c_nationkey_12 values less than(12), 
                partition c_nationkey_13 values less than(13),
                partition c_nationkey_14 values less than(14),
                partition c_nationkey_15 values less than(15),
                partition c_nationkey_16 values less than(16),
                partition c_nationkey_17 values less than(17),
                partition c_nationkey_18 values less than(18),
                partition c_nationkey_19 values less than(19),
                partition c_nationkey_20 values less than(20),
                partition c_nationkey_21 values less than(21),
                partition c_nationkey_22 values less than(22),
                partition c_nationkey_23 values less than(23),
                partition c_nationkey_24 values less than(24),
                partition c_nationkey_25 values less than(25));'''
        msg_customer = self.sh_primy.execut_db_sql(create_customer)
        self.logger.info(msg_customer)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS, msg_customer,
                      '执行失败:' + text)

        text = '---step3.2:切换用户,创建orders表,expect:创建成功 ---'
        self.logger.info(text)
        create_orders = f'''set role {self.user_name} \
            password '{macro.COMMON_PASSWD}';
            drop table if exists {self.table_name_2};
            create table {self.table_name_2}
            (
            o_orderkey       bigint not null, 
            o_custkey        bigint not null, 
            o_orderstatus    char(1) not null, 
            o_totalprice     decimal(15,2) not null, 
            o_orderdate      date not null, 
            o_orderpriority  char(15) not null, 
            o_clerk          char(15) not null, 
            o_shippriority   bigint not null, 
            o_comment        varchar(79) not null
            )
            partition by range(o_orderdate)
            (
            partition o_orderdate_1 values less than('1993-01-01 00:00:00'),
            partition o_orderdate_2 values less than('1994-01-01 00:00:00'),
            partition o_orderdate_3 values less than('1995-01-01 00:00:00'),
            partition o_orderdate_4 values less than('1996-01-01 00:00:00'),
            partition o_orderdate_5 values less than('1997-01-01 00:00:00'),
            partition o_orderdate_6 values less than('1998-01-01 00:00:00'),
            partition o_orderdate_7 values less than('1999-01-01 00:00:00')
            );'''
        msg_orders = self.sh_primy.execut_db_sql(create_orders)
        self.logger.info(msg_orders)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS, msg_orders,
                      '执行失败:' + text)

        text = '---step3.3:切换用户,创建lineitem表,expect:创建成功 ---'
        self.logger.info(text)
        create_lineitem = f'''set role {self.user_name} \
            password '{macro.COMMON_PASSWD}';
            set current_schema to public;
            drop table if exists {self.table_name_3};
            create table {self.table_name_3}
            (
            l_orderkey    bigint not null, 
            l_partkey     bigint not null, 
            l_suppkey     bigint not null, 
            l_linenumber  bigint not null, 
            l_quantity    decimal(15,2) not null, 
            l_extendedprice  decimal(15,2) not null, 
            l_discount    decimal(15,2) not null, 
            l_tax         decimal(15,2) not null, 
            l_returnflag  char(1) not null, 
            l_linestatus  char(1) not null, 
            l_shipdate    date not null, 
            l_commitdate  date not null, 
            l_receiptdate date not null, 
            l_shipinstruct char(25) not null, 
            l_shipmode     char(10) not null, 
            l_comment      varchar(44) not null
            )
            partition by range(l_shipdate)
            (
            partition l_shipdate_1 values less than('1993-01-01 00:00:00'),
            partition l_shipdate_2 values less than('1994-01-01 00:00:00'),
            partition l_shipdate_3 values less than('1995-01-01 00:00:00'),
            partition l_shipdate_4 values less than('1996-01-01 00:00:00'),
            partition l_shipdate_5 values less than('1997-01-01 00:00:00'),
            partition l_shipdate_6 values less than('1998-01-01 00:00:00'),
            partition l_shipdate_7 values less than('1999-01-01 00:00:00')
            ); '''
        msg_lineitem = self.sh_primy.execut_db_sql(create_lineitem)
        self.logger.info(msg_lineitem)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS, msg_lineitem,
                      '执行失败:' + text)

        text = '---step4:导入数据，expect:导入成功---'
        self.logger.info(text)
        mkdir_cmd = f'rm -rf {self.table_path};' \
                    f'mkdir {self.table_path}'
        self.userNode.sh(mkdir_cmd)
        self.common.scp_file(self.userNode, 'customer.tbl',
                             self.table_path)
        self.common.scp_file(self.userNode, 'orders.tbl',
                             self.table_path)
        self.common.scp_file(self.userNode, 'lineitem.tbl',
                             self.table_path)
        customer_path = os.path.join(self.table_path, 'customer.tbl')
        orders_path = os.path.join(self.table_path, 'orders.tbl')
        lineitem_path = os.path.join(self.table_path, 'lineitem.tbl')
        copy_cmd = f'''set role {self.user_name} \
            password '{macro.COMMON_PASSWD}';
            copy {self.table_name_1} from '{customer_path}' \
            using delimiters '|';
            copy {self.table_name_2} from '{orders_path}' \
            using delimiters '|';
            copy {self.table_name_3} from '{lineitem_path}' \
            using delimiters '|';
            analyze;'''
        self.logger.info(copy_cmd)
        copy_msg = self.sh_primy.execut_db_sql(copy_cmd)
        self.logger.info(copy_msg)
        self.assertTrue(self.constant.SET_SUCCESS_MSG and
                        copy_msg.count('COPY') == 3 and
                        self.constant.ANALYZE_SUCCESS_MSG,
                        '执行失败:' + text)

    def test_fullsql(self):
        text = '---step5:设置track_stmt_stat_level的值为OFF,L2,' \
               '设置track_stmt_session_slot为0,使用普通用户执行query,' \
               'expect:参数设置成功，执行query成功---'
        self.logger.info(text)
        sql_cmd = f'''set role {self.user_name} \
                      password '{macro.COMMON_PASSWD}';
            set track_stmt_stat_level='OFF,L2';
            show track_stmt_stat_level;
            set log_min_duration_statement = 0;
            show log_min_duration_statement;
            select l_orderkey, 
                sum(l_extendedprice * (1 - l_discount)) as revenue, 
                o_orderdate, o_shippriority 
            from 
                {self.table_name_1}, 
                {self.table_name_2}, 
                {self.table_name_3} 
            where c_mktsegment = 'BUILDING' 
                and c_custkey = o_custkey 
                and l_orderkey = o_orderkey 
                and o_orderdate < '1995-03-15'::date 
                and l_shipdate > '1995-03-15'::date 
            group by l_orderkey, o_orderdate, o_shippriority 
            order by revenue desc,o_orderdate limit 10;
            select pg_sleep(100);'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertTrue(sql_msg.count(self.constant.SET_SUCCESS_MSG) == 3,
                        '执行失败:' + text)
        self.assertIn('OFF,L2', sql_msg.splitlines()[4], '执行失败:' + text)
        self.assertIn('0', sql_msg.splitlines()[10], '执行失败:' + text)
        self.assertIn('(10 rows)', sql_msg, '执行失败:' + text)

        text = '---step6:查询statement_history系统表信息,' \
               'expect:有记录，日志级别正确---'
        self.logger.info(text)
        sql_cmd = f'''select * 
            from statement_history 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement 
                where query like '%revenue%' 
                    and query_plan is not null and details is not null 
                    and user_name = '{self.user_name}');'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.logger.info(sql_msg)
        self.assertIn('(1 row)', sql_msg, '执行失败:' + text)

        text = '---step8:查询full sql函数,expect:没有记录---'
        self.logger.info(text)
        fullsql_cmd = f'''(select 
                db_name,schema_name,query, query_plan,details 
            from dbe_perf.get_global_full_sql_by_timestamp\
                (clock_timestamp() - 1/24, clock_timestamp()) 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement 
                where query like '%revenue%' and query_plan is not null  
                    and details is not null and user_name = '{self.user_name}')) 
            minus all 
            (select db_name,schema_name,query,query_plan,details 
            from statement_history 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement where query like '%revenue%' 
                    and query_plan is not null and details is not null 
                    and user_name = '{self.user_name}'));'''
        fullsql_msg = self.sh_primy.execut_db_sql(fullsql_cmd,
                                                  dbname='postgres')
        self.logger.info(fullsql_msg)
        self.assertIn('(0 rows)', fullsql_msg, '执行失败:' + text)

        text = '---step9:slow sql函数,expect:没有记录---'
        self.logger.info(text)
        slowsql_cmd = f'''(select * 
            from 
                dbe_perf.get_global_full_sql_by_timestamp\
                (clock_timestamp()-1/24, clock_timestamp()) 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement where query like '%revenue%' 
                    and query_plan is not null  and details is not null 
                    and user_name = '{self.user_name}')) 
            minus all 
            (select * 
            from 
                dbe_perf.get_global_slow_sql_by_timestamp\
                (clock_timestamp()-1/24, clock_timestamp()) 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement where query like '%revenue%' 
                    and query_plan is not null  and details is not null 
                    and user_name = '{self.user_name}')); '''
        slowsql_msg = self.sh_primy.execut_db_sql(slowsql_cmd,
                                                  dbname='postgres')
        self.logger.info(slowsql_msg)
        self.assertIn('(0 rows)', slowsql_msg, '执行失败:' + text)

        text = '---step10:查询statement_history minus all ' \
               'dbe_perf.statement_history的数据,expect:没有记录---'
        self.logger.info(text)
        sql_cmd = f'''(select * 
            from statement_history 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement 
                where query like '%temp_min_ps_supplycost%' 
                    and query_plan is not null  and details is not null 
                    and user_name = '{self.user_name}')) 
            minus all 
            (select * 
            from dbe_perf.statement_history 
            where unique_query_id=(select unique_sql_id 
                from dbe_perf.statement 
                where query like '%temp_min_ps_supplycost%' 
                    and query_plan is not null  and details is not null 
                    and user_name = '{self.user_name}'));'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd,
                                              dbname='postgres')
        self.logger.info(sql_msg)
        self.assertIn('(0 rows)', sql_msg, '执行失败:' + text)

    def tearDown(self):
        text = '---step11:环境清理，expect:成功---'
        self.logger.info(text)
        text = '---检查参数，恢复原值:enable_copy_server_files---'
        self.logger.info(text)
        check_copy = self.sh_primy.execut_db_sql(self.show_copy_cmd)
        self.logger.info(check_copy)
        current = check_copy.splitlines()[-2].strip()
        if self.copy_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_copy}={self.copy_default}')
            self.assertTrue(result, '参数恢复失败')
        show_copy_msg = self.sh_primy.execut_db_sql(self.show_copy_cmd)
        self.logger.info(show_copy_msg)

        text = '---检查参数，恢复原值:instr_unique_sql_count---'
        self.logger.info(text)
        check_instr = self.sh_primy.execut_db_sql(self.show_instr_cmd)
        self.logger.info(check_instr)
        current = check_instr.splitlines()[-2].strip()
        if self.instr_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f'{self.config_instr}={self.instr_default}')
            self.assertTrue(result, '参数恢复失败')
        show_instr_msg = self.sh_primy.execut_db_sql(self.show_instr_cmd)
        self.logger.info(show_instr_msg)

        text = '---删除表，删除用户,删除文件夹---'
        self.logger.info(text)
        drop_cmd = f'''drop owned by {self.user_name} cascade;
                       drop user {self.user_name};'''
        drop_msg = self.sh_primy.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)
        rm_cmd = f'rm -rf {self.table_path};'
        self.rootNode.sh(rm_cmd).result()
        self.assertIn(self.constant.DROP_OWNED_SUCCESS and
                      self.constant.DROP_ROLE_SUCCESS_MSG,
                      drop_msg, '执行失败:' + text)
        self.assertFalse(os.path.exists(self.table_path), '执行失败:' + text)
        self.common.equal_sql_mdg(show_copy_msg, self.config_copy,
                                  self.copy_default, '(1 row)', flag='1')
        self.common.equal_sql_mdg(show_instr_msg, self.config_instr,
                                  self.instr_default, '(1 row)', flag='1')
        self.logger.info(
            '---Opengauss_Function_Fullsql_Case0033 finish---')
